<?php

namespace App\Jobs;

use App\Models\ClosingData;
use App\Models\Exchange;
use App\Models\Message;
use App\Models\Varietie;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\Log;
use PhpOffice\PhpSpreadsheet\IOFactory;

class VarietiesXlsxJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    private $file = null;

    /**
     * @var int
     */
    public $timeout = 7200;

    /**
     * 任务可尝试的次数
     *
     * @var int
     */
    public $tries = 3;

    /**
     * 任务失败前允许的最大异常数
     *
     * @var int
     */
    public $maxExceptions = 3;

    /**
     * XlsxJob constructor.
     * @param $file
     */
    public function __construct($file)
    {
        $this->file = $file;
    }

    /**
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public function handle()
    {
        //        ini_set('memory_limit', '-1');
        //        set_time_limit(3600);
        $msg = '文件名：' . $this->file;
        $title = '品种写入';
        $text = '';
        Log::info($msg . "\n");
        if (!is_file($this->file)) {
            $msg = '没有找到该文件';
            $text .= ' ' . $msg;
            Log::info($msg . "\n");
            Message::writeMessage($title, $text, 1, 0, '', -1);
            return;
        }

        if (strpos($this->file, 'xlsx') === false && strpos($this->file, 'xls') === false && strpos($this->file, 'csv') === false) {
            $msg = '文件后缀不合格，不予处理';
            Log::info($msg . "\n");
            $text .= ' ' . $msg;
            Message::writeMessage($title, $text, 1, 0, '', -1);
            return;
        }
        $objReader = IOFactory::createReader('Xlsx');
        $objPHPExcel = $objReader->load($this->file);  //$filename可以是上传的表格，或者是指定的表格
        $objCount = $objPHPExcel->getSheetCount(); // sheet个数
        $data = [];  // 表格中获取的数据
        $date_time = date('Y-m-d');
        // 存在就覆盖，不存在添加
        $up_num = 0;
        $cr_num = 0;
        // 200条200条的存入数据库,防止数组过大
        $amount = 200;
        $times = 1;
        for ($i = 0; $i < $objCount; $i++) {
            $msg = '处理第' . $i . '个表格';
            Log::info($msg . "\n");
            $sheet = $objPHPExcel->getSheet($i);   //excel中的第i张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            //循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
            for ($j = 2; $j <= $highestRow; $j++) {
                $exchange_name = $sheet->getCellByColumnAndRow(3, $j)->getValue();
                $exchange_code = $sheet->getCellByColumnAndRow(4, $j)->getValue();
                // 查找是否存在该交易所，没有则添加
                $exchange = Exchange::query()
                    ->where('code', $exchange_code)
                    ->where(function ($query) use ($exchange_name) {
                        $query->where('name', $exchange_name)->orWhere('settlement', $exchange_name);
                    })
                    ->first();
                if ($exchange) {
                    $exchange = new Exchange();
                    $exchange->name = $exchange_name;
                    $exchange->description = $exchange_name;
                    $exchange->settlement = $exchange_name;
                    $exchange->code = $exchange_code;
                    $exchange->save();
                }
                if (!$exchange) {
                    continue;
                }
                $oneData = [
                    'exchange_id' => $exchange->id,
                    'title' => $sheet->getCellByColumnAndRow(1, $j)->getValue(),
                    'code' => $sheet->getCellByColumnAndRow(2, $j)->getValue(),
                ];
                $oneData['created_at'] = $date_time;
                $data[] = $oneData;
                if (count($data) >= $amount) {
                    $msg = '...已累计' . $amount * $times . '条' . '，开始写入数据库';
                    Log::info($msg . "\n");
                    foreach ($data as $da) {
                        $info = Varietie::query()
                            ->where('exchange_id', $da['exchange_id'])
                            ->where('code', $da['code'])
                            ->where('title', $da['title'])
                            ->first();
                        if (!$info) {
                            $cr_num++;
                            $res = Varietie::query()->create($da);
                            if (!$res) {
                                $msg = '...写入失败第' . $j . '行';
                                Log::info($msg . "\n");
                            }
                        }
                    }
                    $msg = '...清空$data，继续执行';
                    Log::info($msg . "\n");
                    $times += 1;
                    $data = [];
                }
            }
            // 不到$amount条的情况
            if (count($data) > 0) {
                $msg = '...不到' . $amount . '条';
                Log::info($msg . "\n");
                foreach ($data as $da) {
                    $info = Varietie::query()
                        ->where('exchange_id', $da['exchange_id'])
                        ->where('code', $da['code'])
                        ->where('title', $da['title'])
                        ->first();
                    if (!$info) {
                        $cr_num++;
                        $res = Varietie::query()->create($da);
                        if (!$res) {
                            $msg = '...写入失败第' . $j . '行';
                            Log::info($msg . "\n");
                        }
                    }
                }
                $data = [];
            }
        }
        $msg = '...更新：' . $up_num . '条';
        $text .= ' ' . $msg;
        Log::info($msg . "\n");
        $msg = '...新增：' . $cr_num . '条';
        Log::info($msg . "\n");
        $text .= ' ' . $msg;
        Message::writeMessage($title, $text, 1, 0, '', -1);
        // 删除文件
        unlink($this->file);
        return;
    }
}
